from fastapi import FastAPI, HTTPException, Depends
from sqlmodel import SQLModel, Field, create_engine, Session, select
from typing import List, Optional
from pydantic import BaseModel
import json
import os
from urllib.parse import quote_plus
import hashlib
import pymysql
from fastapi.middleware.cors import CORSMiddleware

app = FastAPI(title="数据库测试应用")

# 添加CORS中间件以支持前后端分离
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # 在生产环境中应该指定具体的域名
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# 数据模型定义
class UserBase(SQLModel):
    username: str = Field(max_length=255, unique=True)
    email: str = Field(max_length=255, unique=True)

# 使用extend_existing=True避免重复定义表的问题
class User(UserBase, table=True):
    __tablename__ = "users"  # 明确指定表名
    __table_args__ = {'extend_existing': True}  # 允许重新定义表
    
    user_id: Optional[int] = Field(default=None, primary_key=True)
    password_hash: str = Field(max_length=255)
    registered_at: Optional[str] = None
    status: int = Field(default=1)

class Section(SQLModel, table=True):
    __tablename__ = "sections"
    __table_args__ = {'extend_existing': True}
    
    section_id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(max_length=255)
    description: Optional[str] = None
    parent_section_id: Optional[int] = Field(default=None, foreign_key="sections.section_id")
    created_at: Optional[str] = None

class Post(SQLModel, table=True):
    __tablename__ = "posts"
    __table_args__ = {'extend_existing': True}
    
    post_id: Optional[int] = Field(default=None, primary_key=True)
    author_id: int = Field(foreign_key="users.user_id")
    section_id: int = Field(foreign_key="sections.section_id")
    content: str
    created_at: Optional[str] = None
    updated_at: Optional[str] = None
    status: int = Field(default=0)

class UserCreate(UserBase):
    password: str = Field(max_length=255)
    
class UserRead(UserBase):
    user_id: int
    registered_at: Optional[str]
    status: int

class LoginRequest(BaseModel):
    username: str
    password: str

class LoginResponse(BaseModel):
    success: bool
    user_id: Optional[int] = None
    message: str

# 板块相关模型
class SectionBase(SQLModel):
    name: str = Field(max_length=255)
    description: Optional[str] = None

class SectionCreate(SectionBase):
    pass

class SectionRead(SectionBase):
    section_id: int
    created_at: Optional[str] = None

# 帖子相关模型
class PostBase(SQLModel):
    content: str
    section_id: int

class PostCreate(PostBase):
    pass

class PostRead(PostBase):
    post_id: int
    author_id: int
    created_at: Optional[str] = None
    updated_at: Optional[str] = None
    status: int

# 读取数据库配置
def load_db_config():
    config_file = os.path.join("conf", "DatabaseInfo.json")
    
    # 如果当前目录找不到，则尝试在上级目录查找
    if not os.path.exists(config_file):
        config_file = os.path.join("..", "conf", "DatabaseInfo.json")
    
    # 再次检查文件是否存在
    if not os.path.exists(config_file):
        raise FileNotFoundError(f"配置文件不存在: {config_file}")
    
    with open(config_file, 'r', encoding='utf-8') as f:
        config = json.load(f)
    return config

# 创建数据库引擎
def create_db_engine():
    try:
        config = load_db_config()
        
        username = config['UserName']
        password = quote_plus(config['PassWord'])
        host = config['IP']
        port = config.get('Port', '3306')
        database = config.get('Database', 'test_db')  # 使用test_db作为默认数据库
        
        # 根据配置类型创建数据库URL
        db_type = config.get('Type', 'mysql')
        if db_type == 'mysql':
            db_url = f"mysql+pymysql://{username}:{password}@{host}:{port}/{database}"
        elif db_type == 'postgresql':
            db_url = f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{database}"
        else:
            raise ValueError(f"不支持的数据库类型: {db_type}")
            
        engine = create_engine(db_url, echo=True, pool_pre_ping=True)
        return engine
    except Exception as e:
        print(f"创建数据库引擎时出错: {e}")
        return None

# 添加创建数据库的函数
def create_database_if_not_exists():
    try:
        config = load_db_config()
        username = config['UserName']
        password = quote_plus(config['PassWord'])
        host = config['IP']
        port = config.get('Port', '3306')
        database = config.get('Database', 'test_db')
        
        # 先连接到默认数据库（如information_schema）来创建新数据库
        connection = pymysql.connect(
            host=host,
            port=int(port),
            user=username,
            password=password,
            database='information_schema',
            charset='utf8mb4'
        )
        
        with connection.cursor() as cursor:
            # 检查数据库是否存在
            cursor.execute("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = %s", (database,))
            result = cursor.fetchone()
            
            # 如果数据库不存在，则创建它
            if not result:
                cursor.execute(f"CREATE DATABASE {database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
                print(f"数据库 {database} 已创建")
            else:
                print(f"数据库 {database} 已存在")
        
        connection.close()
        return True
    except Exception as e:
        print(f"创建数据库时出错: {e}")
        return False

# 初始化数据库
def init_db():
    try:
        # 首先确保数据库存在
        if not create_database_if_not_exists():
            print("无法创建数据库")
            return None
            
        # 然后创建引擎
        engine = create_db_engine()
        if engine:
            # 创建所有表
            SQLModel.metadata.create_all(engine)
            print("所有表已创建或已存在")
        return engine
    except Exception as e:
        print(f"初始化数据库时出错: {e}")
        return None

# 创建全局引擎
engine = init_db()

# 工具函数
def hash_password(password: str) -> str:
    """生成密码哈希"""
    return hashlib.sha256(password.encode()).hexdigest()

def get_session():
    """获取数据库会话"""
    if engine is None:
        raise HTTPException(status_code=500, detail="数据库连接失败")
    with Session(engine) as session:
        yield session

# API路由
@app.get("/")
async def root():
    if engine is None:
        return {"message": "欢迎使用数据库测试应用", "warning": "数据库连接失败，请检查配置"}
    return {"message": "欢迎使用数据库测试应用"}

@app.post("/users/", response_model=UserRead)
async def create_user(user: UserCreate, session: Session = Depends(get_session)):
    try:
        # 检查用户名是否已存在
        statement = select(User).where(User.username == user.username)
        existing_user = session.exec(statement).first()
        if existing_user:
            raise HTTPException(status_code=400, detail="用户名已存在")
        
        # 创建新用户
        db_user = User(
            username=user.username,
            email=user.email,
            password_hash=hash_password(user.password)
        )
        session.add(db_user)
        session.commit()
        session.refresh(db_user)
        return db_user
    except HTTPException:
        raise
    except Exception as e:
        # 提供更详细的错误信息
        error_msg = str(e)
        if "1146" in error_msg or "Table" in error_msg and "doesn't exist" in error_msg:
            raise HTTPException(
                status_code=500, 
                detail="数据库表不存在，请先创建表结构或联系管理员"
            )
        elif "1049" in error_msg or "Unknown database" in error_msg:
            raise HTTPException(
                status_code=500, 
                detail="数据库不存在，请检查配置文件中的数据库名称或联系管理员"
            )
        else:
            raise HTTPException(status_code=500, detail=f"创建用户时出错: {e}")

@app.get("/users/", response_model=List[UserRead])
async def read_users(session: Session = Depends(get_session)):
    try:
        users = session.exec(select(User)).all()
        return users
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"读取用户时出错: {e}")

@app.post("/login/", response_model=LoginResponse)
async def login(credentials: LoginRequest, session: Session = Depends(get_session)):
    try:
        # 查找用户
        statement = select(User).where(User.username == credentials.username)
        user = session.exec(statement).first()
        
        if not user:
            return LoginResponse(
                success=False,
                message="用户名或密码错误"
            )
        
        # 验证密码
        if user.password_hash != hash_password(credentials.password):
            return LoginResponse(
                success=False,
                message="用户名或密码错误"
            )
        
        return LoginResponse(
            success=True,
            user_id=user.user_id,
            message="登录成功"
        )
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"登录时出错: {e}")

# 板块相关API
@app.post("/sections/", response_model=SectionRead)
async def create_section(section: SectionCreate, session: Session = Depends(get_session)):
    try:
        # 获取当前时间
        from datetime import datetime
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        
        # 创建新板块
        db_section = Section(
            name=section.name,
            description=section.description,
            created_at=current_time
        )
        session.add(db_section)
        session.commit()
        session.refresh(db_section)
        return db_section
    except Exception as e:
        session.rollback()
        raise HTTPException(status_code=500, detail=f"创建板块时出错: {e}")

@app.get("/sections/", response_model=List[SectionRead])
async def read_sections(session: Session = Depends(get_session)):
    try:
        sections = session.exec(select(Section)).all()
        return sections
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"读取板块时出错: {e}")

# 帖子相关API
@app.post("/posts/", response_model=PostRead)
async def create_post(post: PostCreate, user_id: int, session: Session = Depends(get_session)):
    try:
        # 获取当前时间
        from datetime import datetime
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        
        # 创建新帖子
        db_post = Post(
            content=post.content,
            section_id=post.section_id,
            author_id=user_id,
            created_at=current_time
        )
        session.add(db_post)
        session.commit()
        session.refresh(db_post)
        return db_post
    except Exception as e:
        session.rollback()
        raise HTTPException(status_code=500, detail=f"创建帖子时出错: {e}")

@app.get("/posts/", response_model=List[PostRead])
async def read_posts(section_id: Optional[int] = None, session: Session = Depends(get_session)):
    try:
        if section_id:
            posts = session.exec(select(Post).where(Post.section_id == section_id)).all()
        else:
            posts = session.exec(select(Post)).all()
        return posts
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"读取帖子时出错: {e}")

if __name__ == "__main__":
    import uvicorn
    import sys
    import os
    
    # 将项目根目录添加到Python路径中
    project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    if project_root not in sys.path:
        sys.path.insert(0, project_root)
    
    # 尝试多个端口
    ports = [11451]
    for port in ports:
        try:
            print(f"正在尝试启动应用，端口: {port}")
            uvicorn.run(
                "app:app",
                host="127.0.0.1",
                port=port,
                reload=True,
                root_path=project_root
            )
            break
        except Exception as e:
            print(f"端口 {port} 启动失败: {e}")
            if port == ports[-1]:  # 最后一个端口也失败了
                print("所有端口都无法使用，请检查是否有防火墙或其他程序阻止了端口绑定")
                sys.exit(1)
